package humanMirror.model.questionLibrary;

import java.sql.*;
import java.util.ArrayList;

public class SqlWorker implements SqlWorkerInterfase{
	private Connection connection;
	private PreparedStatement ps;
	private ResultSet rs;
	private int answerNumber;
    String path = "jdbc:sqlite:HM.sqlite";
//    private String path="jdbc:sqlite:c:\\HM.sqlite";
	
	public SqlWorker()  {
		try {
            Class.forName("org.sqlite.JDBC");
			connection = DriverManager
					.getConnection(path);
			answerNumber = getCountOf("answers");
		} catch (ClassNotFoundException e) {
			System.out.print("Can't connect to database");
			System.exit(0);
		}
		catch (SQLException e) {
			System.out.print("Can't connect to database " + e.toString());
			System.exit(0);
		}

	}

    public SqlWorker(String base)
            throws SQLException {
        connection = DriverManager.getConnection(base);
        answerNumber = getCountOf("answers");
    }

	public String[] getAnswers(int numberOfQuestion)// !+1
			throws SQLDataException, SQLException {
		return getStringArrayResult(numberOfQuestion +1,
				"SELECT answer FROM answers WHERE Question=?", "Answer");
	}

	public String getQuestion(int numberOfQuestion) throws SQLDataException,
			SQLException {
		return getStringResult(numberOfQuestion + 1,
				"SELECT Question FROM questions WHERE Id=?");
	}

	public int getCountOfQuestions() throws SQLException {
		return getCountOf("questions");
	}

	public int currentEffect(int organ, int param, int questionNumber,
			int numberOfAnswer) throws SQLException {
		return getIntResult(queryMaker(param, organ, questionNumber,
				numberOfAnswer));
	}

	// ---------------------------not used yet----------------------------

	int getIntResult(int param, String query) throws SQLException {
		execute(param, query);
		return getIntResult();
	}

	int getIntResult(String query) throws SQLException {
		execute(query);
		int result = 0;
		if (rs.next()) {
			result = rs.getInt(1);
		} else
			throw new SQLDataException(
					"This data does not exist, or is not an int");
		// rs.close();
		return result;
	}

	String getStringResult(int param, String query) throws SQLDataException,
			SQLException {
		execute(param, query);
		return getStringResult();
	}

	String[] getStringArrayResult(int param, String query, String column)
			throws SQLDataException, SQLException {
		execute(param, query);
		return getStringArrayResult(column);
	}

	private void execute(String query) throws SQLException {
		ps = connection.prepareStatement(query);
		rs = ps.executeQuery();

		// ps.close();
	}

	private void execute(int param, String query) throws SQLException {
		ps = connection.prepareStatement(query);
		ps.setInt(1, param);
		rs = ps.executeQuery();

		// ps.close();
	}

	private String getStringResult() throws SQLDataException, SQLException {
		String result = "";
		if (rs.next()) {
			result = rs.getString(1);
		} else
			throw new SQLDataException(
					"This data does not exist, or is not a string");
		// rs.close();
		return result;
	}

	String[] getStringArrayResult(String column) throws SQLDataException,
			SQLException {
		ArrayList<String> answers = new ArrayList<String>();
		while (rs.next()) {
			answers.add(rs.getString(column));
		}
		String[] result = new String[answers.size()];
		answers.toArray(result);
		// rs.close();
		return result;
	}

	private int getIntResult() throws SQLDataException, SQLException {
		int result = 0;
		if (rs.next()) {
			result = rs.getInt(1);
		} else
			throw new SQLDataException(
					"This data does not exist, or is not an int");
		// rs.close();
		return result;
	}

	private int getCountOf(String tableName) throws SQLException {
		execute("SELECT * FROM " + tableName);
		int result = 0;
		while (rs.next()) {
			result = rs.getInt("Id");
		}
		return result;
	}

	public void disconnect() throws SQLException {
		ps.close();
		rs.close();

		connection.close();

	}



	private String chooseOrgan(int organ) {
		String organName = "Brain_";
		switch (organ) {
		case 0:
			organName = "Brain_";
			break;
		case 1:
			organName = "Liver_";
			break;
		case 2:
			organName = "Stomack_";
			break;
		case 3:
			organName = "Lungs_";
			break;
		case 4:
			organName = "Heart_";
			break;
		}
		return organName;
	}

	private String queryMaker(int param, int organ, int questionNumber,
			int answer) {
		StringBuilder query = new StringBuilder();
		query.append("select ");
		query.append(chooseOrgan(organ));
		query.append(param);
		query.append(" from answers where question=");
		query.append(questionNumber + 1);
		query.append(" and answernum=");
		query.append(answer + 1);
		return query.toString();

	}
}
